﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Clinica_Frba.AbmdeRol
{
    public partial class Alta : Form
    {
        public Alta()
        {
            InitializeComponent();
        }

        private void Alta_Load(object sender, EventArgs e)
        {
            string comando = "SELECT ID_FUNC,DESC_FUNC FROM DUNDER_MIFFLIN.FUNCIONALIDAD";
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand consulta=new SqlCommand (comando,myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("id_func");
            dt.Columns.Add("desc_func");
            dt.Load(da);
            listBox1.DisplayMember = "desc_func";
            listBox1.ValueMember = "id_func";
            listBox1.DataSource = dt;
            listBox1.SelectedIndex = 0;
            
            myConnection.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {if (listBox1.Items.Count!=0) {
            bool ver=true;
            int i;
            
            for (i = 0; i < listBox2.Items.Count; i++) {
                if (listBox2.GetItemText(listBox2.Items[i]) == listBox1.GetItemText(listBox1.SelectedItem)) { ver = false; }
            }
                if (ver)
                {

                    listBox2.Items.Add(listBox1.GetItemText(listBox1.SelectedItem));
                    
                }
        }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (listBox2.Items.Count != 0)
            {
                listBox2.Items.Remove(listBox2.SelectedItem);
                if (listBox2.Items.Count > 0) { listBox2.SelectedIndex = 0; }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            listBox2.Items.Clear();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "") { MessageBox.Show("Debe ingresar un nombre para el rol.", "Alta de Roles"); return; }
            if (listBox2.Items.Count == 0) { MessageBox.Show("Debe ingresar al menos una funcionalidad.", "Alta de Roles"); return; }
            int id, i;
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand beg = new SqlCommand("BEGIN TRAN R", myConnection);
            beg.ExecuteNonQuery();
            try{
                SqlCommand prueba = new SqlCommand("SELECT NOMBRE_ROL FROM DUNDER_MIFFLIN.ROL WHERE NOMBRE_ROL='" + textBox1.Text + "'", myConnection);
            SqlDataReader s = prueba.ExecuteReader();
            if (s.Read()) {  MessageBox.Show("Rol repetido", "Alta de roles"); return; }
            s.Close();
            SqlCommand com = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='ROL'", myConnection);
            SqlDataReader SQLR = com.ExecuteReader();
            SQLR.Read();
            id = Convert.ToInt32(SQLR.GetValue(0));
            SQLR.Close();
            id++;
            int idfu;
            SqlCommand insert = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.ROL(ID_ROL,NOMBRE_ROL,ESTADO) VALUES(" + id + ",'" + textBox1.Text + "','H')", myConnection);
            insert.ExecuteNonQuery();
            for (i = 0; i < listBox2.Items.Count; i++) {
                SqlCommand idf = new SqlCommand("SELECT ID_FUNC FROM DUNDER_MIFFLIN.FUNCIONALIDAD WHERE DESC_FUNC='" + listBox2.GetItemText(listBox2.Items[i]) + "'", myConnection);
                SqlDataReader func = idf.ExecuteReader();
                func.Read();
                idfu = Convert.ToInt32(func.GetValue(0));
                func.Close();
                SqlCommand insert2 = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.ROL_FUNCIONALIDAD(ID_ROL,ID_FUNC) VALUES(" + id + "," + idfu + ")", myConnection);
                insert2.ExecuteNonQuery();
            }
            SqlCommand provider = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + id + " WHERE TABLA='ROL'", myConnection);
            
            provider.ExecuteNonQuery();
            SqlCommand c = new SqlCommand("COMMIT TRAN R", myConnection);
            c.ExecuteNonQuery();
            MessageBox.Show("Insercion ok", "Alta de roles");
            this.Close();
            }
            catch(Exception ex){
                MessageBox.Show(ex.Message, "Alta de roles");
                SqlCommand rb = new SqlCommand("ROLLBACK TRAN R", myConnection);
                rb.ExecuteNonQuery();
            }
        }
    }
}
